/*
 * hkh-project
 * leafteamwork.blogspot.com
 * Tool: Netbeans 6.7.1 - SQL Server 2005
 */
package vn.com.hkh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;
import vn.com.hkh.dto.PageDTO;
import vn.com.hkh.util.DatabaseUtil;

/**
 *
 * @author leaf
 */
public class PageDAO {

    // Log
    private static Logger logger = Logger.getLogger(PageDAO.class.getName());
    //C: Create
    private final String SQL_CREATE = "INSERT INTO hkh_page_table(page_id, page_title, username, page_content, page_alias, page_when, page_position, page_status) VALUES (?,?,?,?,?,?,?,?)";
    //R: Read
    private final String SQL_READ_BY_ID = "SELECT page_id, page_title, username, page_content, page_alias, CONVERT(varchar,page_when,103) as page_when, page_position, page_status FROM hkh_page_table WHERE page_id = ?";
    private final String SQL_READ_BY_ALL = "SELECT page_id, page_title, username, page_content, page_alias, CONVERT(varchar,page_when,103) as page_when, page_position, page_status FROM hkh_page_table ORDER BY page_when DESC";
    private final String SQL_READ_BY_STATUS = "SELECT page_id, page_title, username, page_content, page_alias, CONVERT(varchar,page_when,103) as page_when, page_position, page_status FROM hkh_page_table WHERE page_status = ? ORDER BY page_position";
    private final String SQL_READ_BY_ALIAS = "SELECT page_id, page_title, username, page_content, page_alias, CONVERT(varchar,page_when,103) as page_when, page_position, page_status FROM hkh_page_table WHERE page_alias like ? ORDER BY page_position";
    //U: Update
    private final String SQL_UPDATE = "UPDATE hkh_page_table SET page_title=?, username=?, page_content=?, page_alias=?, page_when=?, page_position=?, page_status =? WHERE page_id = ?";
    private final String SQL_UPDATE_STATUS = "UPDATE hkh_page_table SET page_status =? WHERE page_id = ?";
    //D: Delete
    private final String SQL_DELETE_BY_ID = "DELETE FROM hkh_page_table WHERE page_id = ?";

    /**
     * Create new Page
     * @param _pageDTO
     * @return
     */
    public PageDTO create(PageDTO _pageDTO) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_CREATE);

            _ptmt.setString(1, _pageDTO.getId());
            _ptmt.setString(2, _pageDTO.getTitle());
            _ptmt.setString(3, _pageDTO.getUser().getUsername());
            _ptmt.setString(4, _pageDTO.getContent());
            _ptmt.setString(5, _pageDTO.getAlias());
            _ptmt.setString(6, _pageDTO.getWhen());
            _ptmt.setInt(7, _pageDTO.getPosition());
            _ptmt.setInt(8, _pageDTO.getStatus());

            _ptmt.executeUpdate();
            _ptmt.close();
            return _pageDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read Page by Id
     * @param _id
     * @return PageDTO
     */
    public PageDTO readByID(String _id) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        PageDTO _pageDTO = null;
        try {

            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_READ_BY_ID);
            _ptmt.setString(1, _id);

            ResultSet _rs = _ptmt.executeQuery();
            if (_rs.next()) {
                _pageDTO = new PageDTO();
                _pageDTO.setId(_rs.getString(1));
                _pageDTO.setTitle(_rs.getString(2));
                _pageDTO.setUser(new UserDAO().readById(_rs.getString(3)));
                _pageDTO.setContent(_rs.getString(4));
                _pageDTO.setAlias(_rs.getString(5));
                _pageDTO.setWhen(_rs.getString(6));
                _pageDTO.setPosition(_rs.getInt(7));
                _pageDTO.setStatus(_rs.getInt(8));

            }
            _rs.close();
            _ptmt.close();
            return _pageDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read Page by All
     * @return Collection<PageDTO>
     */
    public Collection<PageDTO> readByAll() {
        Collection<PageDTO> _list = null;
        Connection _con = null;
        Statement _stmt = null;
        PageDTO _pageDTO = null;
        try {
            _con = DatabaseUtil.getConnection();
            _stmt = _con.createStatement();
            ResultSet _rs = _stmt.executeQuery(SQL_READ_BY_ALL);

            _list = new ArrayList<PageDTO>();
            while (_rs.next()) {
                _pageDTO = new PageDTO();
                _pageDTO.setId(_rs.getString(1));
                _pageDTO.setTitle(_rs.getString(2));
                _pageDTO.setUser(new UserDAO().readById(_rs.getString(3)));
                _pageDTO.setContent(_rs.getString(4));
                _pageDTO.setAlias(_rs.getString(5));
                _pageDTO.setWhen(_rs.getString(6));
                _pageDTO.setPosition(_rs.getInt(7));
                _pageDTO.setStatus(_rs.getInt(8));

                _list.add(_pageDTO);
            }
            _rs.close();
            _stmt.close();
            return _list;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Rea Page by Status
     * @param _status
     * @return Collection<PageDTO>
     */
    public Collection<PageDTO> readByStatus(int _status) {
        Collection<PageDTO> _list = null;
        Connection _con = null;
        PreparedStatement _ptmt = null;
        PageDTO _pageDTO = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_READ_BY_STATUS);
            _ptmt.setInt(1, _status);

            ResultSet _rs = _ptmt.executeQuery();
            _list = new ArrayList<PageDTO>();
            while (_rs.next()) {
                _pageDTO = new PageDTO();
                _pageDTO.setId(_rs.getString(1));
                _pageDTO.setTitle(_rs.getString(2));
                _pageDTO.setUser(new UserDAO().readById(_rs.getString(3)));
                _pageDTO.setContent(_rs.getString(4));
                _pageDTO.setAlias(_rs.getString(5));
                _pageDTO.setWhen(_rs.getString(6));
                _pageDTO.setPosition(_rs.getInt(7));
                _pageDTO.setStatus(_rs.getInt(8));

                _list.add(_pageDTO);
            }
            _rs.close();
            _ptmt.close();
            return _list;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read Page by Alias
     * @param _alias
     * @return Collection<PageDTO>
     */
    public Collection<PageDTO> readByAlias(String _alias) {
        Collection<PageDTO> _list = null;
        Connection _con = null;
        PreparedStatement _ptmt = null;
        PageDTO _pageDTO = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_READ_BY_ALIAS);
            _ptmt.setString(1, _alias);

            ResultSet _rs = _ptmt.executeQuery();
            _list = new ArrayList<PageDTO>();
            while (_rs.next()) {
                _pageDTO = new PageDTO();
                _pageDTO.setId(_rs.getString(1));
                _pageDTO.setTitle(_rs.getString(2));
                _pageDTO.setUser(new UserDAO().readById(_rs.getString(3)));
                _pageDTO.setContent(_rs.getString(4));
                _pageDTO.setAlias(_rs.getString(5));
                _pageDTO.setWhen(_rs.getString(6));
                _pageDTO.setPosition(_rs.getInt(7));
                _pageDTO.setStatus(_rs.getInt(8));

                _list.add(_pageDTO);
            }
            _rs.close();
            _ptmt.close();
            return _list;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Update Page
     * @param _pageDTO
     * @return PageDTO
     */
    public PageDTO update(PageDTO _pageDTO) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_UPDATE);

            _ptmt.setString(1, _pageDTO.getTitle());
            _ptmt.setString(2, _pageDTO.getUser().getUsername());
            _ptmt.setString(3, _pageDTO.getContent());
            _ptmt.setString(4, _pageDTO.getAlias());
            _ptmt.setString(5, _pageDTO.getWhen());
            _ptmt.setInt(6, _pageDTO.getPosition());
            _ptmt.setInt(7, _pageDTO.getStatus());
            _ptmt.setString(8, _pageDTO.getId());

            _ptmt.executeUpdate();
            _ptmt.close();
            return _pageDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Update Status
     * @param _pageId
     * @param _status
     * @return boolean
     */
    public boolean updateStatus(String _pageId, int _status) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_UPDATE_STATUS);

            _ptmt.setInt(1, _status);
            _ptmt.setString(2, _pageId);
            _ptmt.executeUpdate();

            _ptmt.close();
            return true;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return false;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Delete Page
     * @param _pageId
     * @return boolean
     */
    public boolean deleteById(String _pageId) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_DELETE_BY_ID);
            _ptmt.setString(1, _pageId);
            _ptmt.executeUpdate();

            _ptmt.close();
            return true;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return false;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }
}
